from django.shortcuts import render,redirect,reverse
from django.http import HttpResponse,JsonResponse
from django.db import connection
from django.contrib.auth.decorators import login_required
from django.contrib.auth import login, authenticate,logout
from django.contrib.auth.models import User
from .form import *
from django.core.paginator import Paginator
from django.core.paginator import PageNotAnInteger
from django.core.paginator import EmptyPage
from computerstatus.models import *
from networkstatus.models import *
from django.views.decorators.csrf import csrf_exempt
import platform
import os
import time
import xlwt

# Create your views here.
# 系统管理 - 资产管理
@login_required(login_url="login.html")
def indexView(request):
    # infos['sel_ziduan1'].initial = [2]
    # 默认刷新表格最后一个 select_table
    # t 为日期表索引
    # s 为搜索输入框
    # n 类别索引选择
    # 默认不输入值，会初始化为字符串None
    # 接口类型使用的都是GET
    cursor = connection.cursor()
    t = request.GET.get('t', '')
    s = request.GET.get('s', '')
    n = request.GET.get('n', '')
    # 表名初始化
    if t == "默认最后时间" or t == 'None' or not t :
        t = NumberTable.objects.all().last().tab_name
    # 查询字段初始化
    if s == 'None':
        data = request.GET.copy()
        data['s'] = ''
        print(data)
        request.GET = data
    # 处理完后，初始化form 表格
    infos = SearchForm(request.GET)
    # 测试打印
    # print(t, s, n, "==" * 25)

    if s and s != 'None':
        cursor.execute("""select * from `{}` where {} like '%{}%'""".format(t,n,s))
    else:
        cursor.execute("""select * from `{}`""".format(t))

    # 获取数据库所有符合条件的
    show_value = cursor.fetchall()
    # 前端 p 字段 为传递当前分页值
    p = request.GET.get('p',1)
    # 分页可以修改
    # 系统处理分页
    paginator = Paginator(show_value,7)
    titleClass = "系统管理 - 资产管理"
    shu_num = 1
    ye_num = 1
    try:
        pages = paginator.page(p)
    except PageNotAnInteger:
        pages = paginator.page(1)
    except EmptyPage:
        pages = paginator.page(paginator.num_pages)
    return render(request,'index.html',locals())


# 修改
@login_required(login_url="login.html")
def indexEdit(request):
    # 资产编号 number
    # 资产名称 name
    # 资产型号 category
    # 数    量 num
    # 资产分类 table
    # 具体位置 seat_number
    # 使 用 者 asset_users
    # 备    注 remarks
    # 使用ajax 发送POST请求进行更新
    if request.method == "POST":
        number = request.POST.get('number',"")
        name = request.POST.get('name',"")
        category = request.POST.get('category',"")
        num = request.POST.get('num',"")
        table = request.POST.get('table',"")
        seat_number = request.POST.get('seat_number',"")
        asset_users = request.POST.get('asset_users',"")
        remarks = request.POST.get('remarks',"")
        in_tab = request.POST.get('in_tab',"")
        mac = request.POST.get('mac',"")
        if in_tab == '默认最后时间' or in_tab == 'None':
            in_tab = NumberTable.objects.all().last().tab_name
        sql = """UPDATE `{}` SET asset_name='{}',category='{}',seat_number='{}',asset_users='{}',remarks='{}' WHERE it_number='{}'""".format(
            in_tab, name, category, seat_number, asset_users,remarks, number)

        print(sql)
        print("==="*25)
        print(table)
        cursor = connection.cursor()
        print("资产编号",number,"资产名称",name,"资产类型",category,"数量",num,"资产分类",table,"具体位置",seat_number,
              "使用者",asset_users,"备注",remarks,"资产盘点表",in_tab)
        flag = cursor.execute(sql)
        if not flag:
            return HttpResponse("更新错误:",in_tab)
        # 后期要改，用ORM的方式
        if table == "computerstatus_ipassets":
            sql = """UPDATE `{}` SET ip_name='{}',ip_category='{}',ip_seat_number='{}',ip_asset_users='{}',ip_remarks='{}' WHERE ip_number='{}'""".format(
                table,name, category, seat_number, asset_users, remarks, number)
        elif table == "computerstatus_oassets":
            sql = """UPDATE `{}` SET o_name='{}',o_category='{}',o_seat_number='{}',o_asset_users='{}',o_remarks='{}' WHERE o_number='{}'""".format(
                table,name, category, seat_number, asset_users, remarks, number)
        # if table == "computerstatus_ipassets" and mac:
        #     mac = mac.replace("：",":")
        #     sql = """UPDATE `{}` SET ip_name='{}',ip_category='{}',ip_seat_number='{}',ip_asset_users='{}',ip_mac='{}',ip_remarks='{}' WHERE ip_number='{}'""".format(
        #         table, name, category, seat_number, asset_users,mac ,remarks, number)
        flag = cursor.execute(sql)

        # 可以优化MAC地址添加的自动化排错功能

        if not flag:
            return HttpResponse("更新错误:", table)
        return JsonResponse({'status':200,'msg':'修改成功'})


# 登录
def loginView(request):
    titleClass = "系统管理 - 登录"
    infos = LoginForm()
    if request.method == "POST":
        username = request.POST.get('username','')
        password = request.POST.get('password','')

        if User.objects.filter(username=username):

            user = authenticate(username=username,password=password)

            if user:
                login(request,user)
                return redirect(reverse("index:index"))

            else:
                print("登录失败")
    return render(request, "login.html",locals())


# 退出登录
def logoutView(request):
    logout(request)
    return redirect(reverse("index:index"))


# 生成盘点资产表,后期可以优化
# 其他管理 - 盘点生成
def InventorySheet(request):
    # 创建以当前时间为表的名字，和建立索引
    shu_num = 3
    ye_num = 12
    titleClass = "其他管理 - 盘点生成(IT专用)"
    print("进入生成模块")
    table_name = "table_" + time.strftime('%Y-%m-%d_%H_%M', time.localtime())
    sql = """insert into computerstatus_numbertable(tab_name) value ('{}')""".format(table_name)
    cursor = connection.cursor()
    cursor.execute(sql)

    # print(flag)
    try:
        sql = '''CREATE TABLE `{0}` (`it_number` VARCHAR(255),`asset_users` VARCHAR(255),`asset_name` VARCHAR(255),
        `seat_number` VARCHAR(255),`category` VARCHAR(255),`it_table` VARCHAR (255),`remarks` VARCHAR (255), `flag` VARCHAR (1) )'''.format(table_name)
        print(sql)
        cursor.execute(sql)

        sql = """SELECT ip_number,ip_asset_users,ip_name,ip_seat_number,ip_category,'computerstatus_ipassets',ip_remarks,'0' FROM computerstatus_ipassets 
        union all SELECT o_number,o_asset_users,o_name,o_seat_number,o_category,'computerstatus_oassets',o_remarks,'0' FROM computerstatus_oassets"""
        cursor.execute(sql)
        all = cursor.fetchall()
        print(type(all))
        a = str(all)[1:-1]
        sql = """insert into `{}`(it_number,asset_users,asset_name,seat_number,category,it_table,remarks,flag) value {}""".format(table_name,a)
        cursor.execute(sql)

    except Exception as e:
        print(e)
        return JsonResponse({'code': '500', 'msg': '更新失败'})

    return JsonResponse({'code':'200','msg':'更新成功'})


# 编辑保存页面
def statusok(request):
    t = request.POST.get('t', '')
    s = request.POST.get('it_number','')
    if t == "默认最后时间" or t == 'None' or not t:
        t = NumberTable.objects.all().last().tab_name

    sql = """UPDATE `{}` SET flag="1" WHERE it_number='{}'""".format(
        t,s)
    cursor = connection.cursor()
    cursor.execute(sql)

    return JsonResponse({"status":"200","msg":"更新成功"})


# 自动在线登录接口
@csrf_exempt
def loginAction(request):
    # 自动汇报在线接口方式：POST
    if request.method == 'POST':
        post_string = request.POST
        print(post_string)
        request.encoding = 'gb2312'
        print(request.POST)
        # POST 字符串编码的异常处理
        if len(request.POST) == 0:
            print("进入乱码还原")
            request.encoding = 'utf-8'
            request.POST = post_string
            print(request.POST)

        # 通过ping命令处理判断多张网卡
        c_network1 = request.POST.get('c_network','').split(',')
        c_add = ""
        c_add1 = ""
        sysstr = platform.system()
        for x in range(0, len(c_network1)):
            if '192.168.' in c_network1[x]:
                c_add = c_network1[x - 1]
                c_add1 = c_network1[x]
                if sysstr == "Windows":
                    if os.system('ping -n 1 -w 1 %s' % c_add1) == 0:
                        print(c_add,c_add1)
                        break;

                else:
                    if os.system('ping -c 1 -W 1 %s' % c_add1) == 0:
                        break;

        # 清洗完毕，开始写入数据库
        name = request.POST.get('name','')
        name_yu = request.POST.get('name_yu', '')
        computer_name = request.POST.get('computer_name','')
        system_type = request.POST.get('system_type','')
        c_cpu = request.POST.get('c_cpu','')
        c_memory = request.POST.get('c_memory','')
        c_display = request.POST.get('c_display','')
        c_date = request.POST.get('c_date','')
        c_time = time.strftime("%Y-%m-%d %H:%M:%S")
        print(c_add,c_add1)
        it_number = " "
        seat_number = " "
        asset_users = " "
    # 需要判断v是否有值,此处还需要后期完善
    try:
        v = IpAssets.objects.get(ip_mac=c_add)
        print(v.id)
        it_number = v.ip_number
        seat_number = v.ip_seat_number
        asset_users = v.ip_asset_users
    except Exception as e:
        print(e)
        print("更新失败")
        return HttpResponse("更新失败")
    d = dict(name=name,name_yu=name_yu,computer_name=computer_name,system_type=system_type,c_cpu=c_cpu,c_memory=c_memory,
             c_network=c_add1,mac_network=c_add,c_display=c_display,c_date=c_date,it_number=it_number,seat_number=seat_number,asset_users=asset_users)

    Computer.objects.create(**d)
    d = dict(name=name, name_yu=name_yu, computer_name=computer_name, system_type=system_type, c_cpu=c_cpu,
             c_memory=c_memory,
             c_network=c_add1, mac_network=c_add, c_display=c_display, c_date=c_date, it_number=it_number,
             seat_number=seat_number, asset_users=asset_users,c_time=c_time)

    ComputerStatus.objects.filter(mac_network=c_add).update(**d)
    # ComputerStatus.objects.update_or_create(d1,defaults=**d)
    # ComputerStatus.objects.all()

    d = dict(it_number=it_number,seat_number=seat_number,asset_users=asset_users,mac_network=c_add,c_network = c_add1,auto_test="自动提交",c_time=c_time)

    TestComputer.objects.create(**d)

    return HttpResponse("更新成功")


# 资产报废模块
def crapAction(request):
    # t 下拉框中需要报废的，意指当前
    # n 根目录下需要报废的。
    # s 为资产编号
    # l 为类别
    t = request.POST.get("in_tab", "")
    n = request.POST.get("table", "")
    s = request.POST.get("it_number", "")
    l = request.POST.get("leibie", "")
    # 删除当前表和根目录表数据，并且写入报废列表中。
    if t == "默认最后时间" or t == 'None' or not t :
        t = NumberTable.objects.all().last().tab_name

    sql = """DELETE FROM `{}` WHERE it_number='{}'""".format(t,s)
    cursor = connection.cursor()
    cursor.execute(sql)

    if n == "computerstatus_ipassets":
        sql = """DELETE FROM {} WHERE ip_number='{}'""".format(n, s)
    else:
        sql = """DELETE FROM {} WHERE o_number='{}'""".format(n, s)

    cursor = connection.cursor()
    cursor.execute(sql)

    scrp = Scrap(number=s,category=l)
    scrp.save()

    return redirect(reverse("index:index"))


# 系统管理 - 报废清单
def showCrap(request):
    shu_num = 1
    ye_num = 4
    # 重复利用tabshow显示，list1为数据，listtab为表头
    titleClass = "系统管理 - 报废清单"
    list1 = Scrap.objects.all().values_list()
    listtab = ['序号','资产编号','资产类型','报废时间']

    return render(request,"tabshow.html",locals())


def TestNetwork(request):
    print(request)


def AddUser(request):
    shu_num = 2
    ye_num = 5
    titleClass = "新增管理账号"
    if request.method == "POST":
        user = request.POST.get("username","")
        psw = request.POST.get("password","")
        psw1 = request.POST.get("password1","")
        if psw == psw1:
            if User.objects.filter(username=user):
                adduser = UserForm()
                return render(request, "adduser.html", locals())
            else:
                d = dict(username=user,password=psw,is_staff=1,is_active=1)
                user = User.objects.create_user(**d)
                print(user.save())
                return redirect(reverse(("index:index")))
        else:
            adduser = UserForm()
            return render(request, "adduser.html", locals())
    adduser = UserForm()
    return render(request, "adduser.html", locals())


@csrf_exempt
def Dowload(request):
    shu_num = 1
    ye_num = 2
    titleClass = "资产表下载"
    if request.method == "GET":
        infos = SearchForm()
    else:
        t = request.POST.get("t",'')
        if t == "默认最后时间" or t == 'None' or not t:
            t = NumberTable.objects.all().last().tab_name
        cursor = connection.cursor()
        cursor.execute("""select * from `{}`""".format(t))
        show_value = cursor.fetchall()
        style_head = xlwt.XFStyle()  # 初始化样式
        font = xlwt.Font()  # 初始化字体相关
        font.name = "微软雅黑"
        font.bold = True
        font.colour_index = 1

        bg = xlwt.Pattern()  # 初始背景图案
        bg.pattern = xlwt.Pattern.SOLID_PATTERN  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
        bg.pattern_fore_colour = 4  # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray

        # 设置字体
        style_head.font = font
        # 设置背景
        style_head.pattern = bg

        # 创建一个excel
        excel = xlwt.Workbook()
        # 添加工作区
        sheet = excel.add_sheet("资产盘点")

        # 标题信息
        head = ['资产编号', '使用者', '资产名称', '具体位置', '资产类别', '类别', '所属公司', '盘点状态']
        for index, value in enumerate(head):
            sheet.write(0, index, value, style_head)

        for index, value_list in enumerate(show_value, 1):
            for i, value in enumerate(value_list):
                if value == "1":
                    value = "已确认"
                elif value == "computerstatus_ipassets":
                    value = "电子类"
                elif value == "computerstatus_oassets":
                    value = "家具类"
                sheet.write(index, i, value)
        excel.save("static/write.xls")

    return render(request,"dowload.html",locals())


def excelDowload(request):

    file = open('static/write.xls', 'rb')
    response = HttpResponse(file)
    response['Content-Type'] = 'application/octet-stream'  # 设置头信息，告诉浏览器这是个文件
    response['Content-Disposition'] = 'attachment;filename="write.xls"'
    return response


@csrf_exempt
@login_required(login_url="login.html")
def virtualView(request):
    # t 为索引选择
    # s 为搜索输入框
    # p 为分页
    t = request.GET.get('t', '')
    s = request.GET.get('s', '')
    p = request.GET.get('p',1)
    infos = virtualSearchForm(request.GET)
    tab = 'computerstatus_vassets'
    shu_num = 1
    ye_num = 3
    titleClass = "虚拟资产"
    cursor = connection.cursor()
    if s and s != 'None':
        cursor.execute("""select * from `{}` where {} like '%{}%'""".format(tab,t,s))
    else:
        cursor.execute("""select * from `{}`""".format(tab))

    show_value = cursor.fetchall()
    paginator = Paginator(show_value,7)
    try:
        pages = paginator.page(p)
    except PageNotAnInteger(1):
        pages = paginator.page(1)
    except EmptyPage:
        pages = paginator.page(paginator.num_pages)

    return render(request, "virtual.html", locals())


@csrf_exempt
def virtualViewAPI(request):
    if request.method == 'POST':
        api = request.POST.get('api',"")
        ip = request.POST.get('ip', "")
        mac = request.POST.get('mac', "")
        cpu = request.POST.get('cpu', "")
        memony = request.POST.get('memony', "")
        harddisk = request.POST.get('harddisk', "")
        name = request.POST.get('name','')
        system = request.POST.get('system', "")
        hostname = request.POST.get('hostname', "")
        remarks = request.POST.get('remarks', "")
        # 排错暂时没有写
        if api == '2':
            ip = ip.strip().replace('。','.')
            mac = mac.strip().replace('：',':')
            if len(mac) != 17:
                return JsonResponse({"status": "400", "msg": "更新错误，请填写正确的MAC地址"})
            elif VAssets.objects.filter(v_mac=mac):
                return JsonResponse({"status": "400", "msg": "更新错误，MAC地址有重复"})

            v = VAssets()
            v.v_ip = ip
            v.v_mac = mac
            v.v_cpu = cpu
            v.v_Name = name
            v.v_memory = memony
            v.v_hardDisk = harddisk
            v.v_system = system
            v.v_hostName = hostname
            v.v_remarks = remarks
            v.save()
            return JsonResponse({"status": "200", "msg": "更新成功"})
        elif api == '1':
            v = VAssets.objects.get(v_mac=mac)
            v.v_cpu = cpu
            v.v_Name = name
            v.v_memory = memony
            v.v_hardDisk = harddisk
            v.v_system = system
            v.v_hostName = hostname
            v.v_remarks = remarks
            v.save()
            return JsonResponse({"status": "200", "msg": "更新成功"})
        elif api == '3':
            v = VAssets.objects.get(id=ip)
            s = Scrap()
            s.number = v.v_ip
            s.category = v.v_mac
            s.save()
            v.delete()
            return redirect(reverse("index:virtual"))

    pass
